package com.vl.excel.parseexcel.parsevl;

import com.vl.excel.parseexcel.config.VlConfig;
import com.vl.excel.parseexcel.exception.NoAVLException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;


@Component
public class VlExcelParser {
    private static final Logger log = LoggerFactory.getLogger(VlExcelParser.class);
    @Autowired
    VlConfig vlConfig;
    private Map<String, String> accountMap = new HashMap<>();

    public void parse() throws NoAVLException {
        log.info("path info>> {}", vlConfig);
        accountMap = getAccountVlMap(vlConfig.getCategoryPath());
        String sDir = vlConfig.getSourcePath();
        Path sDP = Path.of(sDir);
        if (!Files.exists(sDP)) {
            System.err.println("不存在文件>" + sDir);
            return;
        }
        String outputPath = vlConfig.getOutputPath();
        if (Files.isDirectory(sDP)) {
            File[] files = sDP.toFile().listFiles();
            if (Objects.isNull(files)) {
                log.error("没有找到excel文件>{}", sDir);
                System.err.println("没有找到excel文件>" + sDir);
                return;
            }
            long now = System.currentTimeMillis();
            long l2 = now / 1000 / 60 / 60 / 24;
            for (File file : files) {
                try {
                    long l = Files.getLastModifiedTime(Path.of(file.getAbsolutePath())).toMillis();
                    long l1 = l / 1000 / 60 / 60 / 24;
                    if (l1 != l2) {
                        System.err.println("不是当天的文件不处理>" + file.getAbsolutePath());
                        continue;
                    }
                } catch (IOException e) {
                    log.error("获取文件修改时间错误:", e);
                    System.err.println("获取文件修改时间错误>" + file.getAbsolutePath());
                    continue;
                }
                if (file.isFile()) {
                    parse(file.getAbsolutePath(), outputPath);
                }
            }

        } else {
            parse(sDP.toFile().getAbsolutePath(), outputPath);
        }
    }

    public void parse(String sFilePath, String dDir) throws NoAVLException {
        System.out.println("开始分表>" + sFilePath);
        Map<String, XSSFWorkbook> allNewBook = new HashMap<>();
        Map<String, XSSFWorkbook> avlNewBookMap = new HashMap<>();
        File file = new File(sFilePath);
        Path destDir = Path.of(String.join(File.separator, dDir, file.getName().substring(0, file.getName().lastIndexOf("."))));
        try {
            if (Files.exists(destDir)) {
                System.out.println("已存在分表文件> " + destDir);
                return;
            }
            InputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            int firstRowNum = sheet.getFirstRowNum();
            XSSFRow firstRow = sheet.getRow(firstRowNum);
            for (int i = firstRowNum + 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                if (Objects.nonNull(row)) {
                    XSSFCell vlCell = row.getCell(7);
                    if (Objects.nonNull(vlCell)) {
                        String vlName = vlCell.getStringCellValue().trim();
                        XSSFWorkbook nBook = allNewBook.get(vlName);
                        if (Objects.isNull(nBook)) {
                            nBook = createNewBook(firstRow);
                            allNewBook.put(vlName, nBook);
                        }
                        //hk vl 单表+分类
                        if (StringUtils.hasText(vlName) && vlName.startsWith("HONG")) {
                            XSSFCell idCell = row.getCell(1);
                            String id = idCell.getStringCellValue().trim();
                            if (!StringUtils.hasText(id)) {
                                continue;
                            }
                            String avlName = accountMap.get(id);
                            if (!StringUtils.hasText(avlName)) {
                                throw new NoAVLException(id);
                            }
                            XSSFCell atcell = row.createCell(row.getLastCellNum());
                            atcell.setCellValue(avlName);
                            copyRow(nBook.getSheetAt(0), row);
                        } else {
                            copyRow(nBook.getSheetAt(0), row);
                        }
                        //hk vl 多表
                        if (StringUtils.hasText(vlName) && vlName.startsWith("HONG")) {
                            XSSFCell idCell = row.getCell(1);
                            String id = idCell.getStringCellValue().trim();
                            if (!StringUtils.hasText(id)) {
                                continue;
                            }
                            String avlName = accountMap.get(id);
                            if (!StringUtils.hasText(avlName)) {
                                throw new NoAVLException(id);
                            }
                            XSSFWorkbook avlNBook = avlNewBookMap.get(avlName + "消耗汇总");
                            if (Objects.isNull(avlNBook)) {
                                avlNBook = createNewBook(firstRow);
                                avlNewBookMap.put(avlName + "消耗汇总", avlNBook);
                            }
                            copyRow(avlNBook.getSheetAt(0), row);
                        }
                    }
                }
            }
            saveAllBook(allNewBook, file.getName(), dDir, "");
            saveAllBook(avlNewBookMap, file.getName(), dDir, "hkvl");
            workbook.close();
            inputStream.close();
            System.out.println("所有分表完成.");
        } catch (IOException e) {
            try {
                Files.deleteIfExists(destDir);
            } catch (IOException ex) {
                log.error("删除文件错误：", ex);
            }
            log.error("解析错误：", e);
        }
    }

    private void saveAllBook(Map<String, XSSFWorkbook> allNewBook, String sfname, String dDir, String vlDirName) throws IOException {
        String fDirName = sfname.substring(0, sfname.lastIndexOf("."));
        String outDir = String.join(File.separator, dDir, fDirName, vlDirName);
        Files.createDirectories(Path.of(outDir));
        System.out.println("开始写入到文件夹:" + outDir);
        allNewBook.forEach((vlName, book) -> {
            try (FileOutputStream fileOut = new FileOutputStream(String.join(File.separator, outDir, vlName + ".xlsx"))) {
                book.write(fileOut);
                book.close();
            } catch (Exception e) {
                log.error("写入excel错误：", e);
                System.err.println("写入excel文件错误:" + e.getMessage());
            }
        });
    }

    public XSSFWorkbook createNewBook(XSSFRow firstRow) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        copyRow(sheet, firstRow);
        return workbook;
    }

    public void copyRow(XSSFSheet sheet, XSSFRow orow) {
        if (Objects.isNull(sheet)) {
            log.error("map获取sheet为空");
            System.err.println("复制表格为空");
            return;
        }
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum <= -1) {
            lastRowNum = 0;
        } else {
            lastRowNum += 1;
        }
        XSSFRow nrow = sheet.createRow(lastRowNum);
//        CellStyle rowStyle = orow.getRowStyle();
//        if (Objects.nonNull(rowStyle)) {
//            nrow.setRowStyle(rowStyle);
//        }
        for (int i = orow.getFirstCellNum(); i <= orow.getLastCellNum(); i++) {
            XSSFCell cell = orow.getCell(i);
            XSSFCell nCell = nrow.createCell(i);
            if (Objects.nonNull(cell)) {
//                XSSFCellStyle cellStyle = cell.getCellStyle();
//                if (Objects.nonNull(cellStyle)) {
//                    nCell.setCellStyle(cellStyle);
//                }
                switch (cell.getCellType()) {
                    case STRING:
                        nCell.setCellValue(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        nCell.setCellValue(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        nCell.setCellValue(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        nCell.setCellValue(cell.getCellFormula());
                        break;
                    default:
                        nCell.setCellValue(cell.getStringCellValue());
                        System.out.println("Cell-Unknown type, treating as String value: " + cell.getStringCellValue());
                }
            }
        }
    }

    public Map<String, String> getAccountVlMap(String sFilePath) {
        Map<String, String> accountMap = new HashMap<>();
        try {
            File file = new File(sFilePath);
            InputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            int firstRowNum = sheet.getFirstRowNum();
            for (int i = firstRowNum + 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                if (Objects.nonNull(row)) {
                    XSSFCell idCell = row.getCell(0);
                    XSSFCell vlCell = row.getCell(4);
                    if (Objects.nonNull(idCell)) {
                        String idName = idCell.getStringCellValue().trim();
                        String vlName = vlCell.getStringCellValue().trim();
                        accountMap.put(idName, vlName);
                    }
                }
            }
        } catch (Exception e) {
            log.error("解析账户信息错误：", e);
        }
        return accountMap;
    }
}
